﻿using Net.Sz.Framework.DB;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Net.Sz.Framework.ExcelTools.CreateCode.excel.mysql
{
    /// <summary>
    /// 
    /// <para>PS:</para>
    /// <para>@author 失足程序员</para>
    /// <para>@Blog http://www.cnblogs.com/shizuchengxuyuan/</para>
    /// <para>@mail 492794628@qq.com</para>
    /// <para>@phone 13882122019</para>
    /// </summary>
    public class CreateMysql : CreateBase
    {
        static readonly CreateMysql instance = new CreateMysql();
        public static CreateMysql Instance()
        {
            return instance;
        }

        protected override void CreateClassFile(string fileName, string msg)
        {
            base.CreateClassFile(MySqlPath + "/" + fileName + DateTime.Now.ToString("_yyyy_MM_dd_HH_mm_ss") + ".sql", msg);
        }

        public void ActionExcel(ExcelDatas exceldatas, string cellgs, string useStr, bool inDB, Action<string> show)
        {
            try
            {
                StringBuilder builder = new StringBuilder();
                if (inDB)
                {
                    builder.AppendLine("USE " + useStr + ";");
                }
                CreateTableHeadInster(builder, exceldatas, cellgs, useStr, inDB, show);
                CreateTableRowInster(builder, exceldatas, cellgs, useStr, inDB, show);
                if (inDB)
                {
                    try
                    {
                        show("开始写入数据库----可能时间比较长....");
                        Dal impl = new MysqlImpl(FormMain.Dbconfig.DBPath, FormMain.Dbconfig.DBPart, FormMain.Dbconfig.DBBase, FormMain.Dbconfig.DBUser, FormMain.Dbconfig.DBPwd, false);
                        int count = impl.ExecuteQuery(builder.ToString());
                        show("写入数据 影响行数 " + count);
                    }
                    catch (Exception ex)
                    {
                        show("数据库操作失败：" + ex.ToString());
                        return;
                    }
                }
                else
                {
                    CreateClassFile("mysql", builder.ToString());
                }
            }
            catch (Exception ex)
            {
                show("请仔细检查配置表 错误信息：" + ex.ToString());
            }
        }

        public bool CreateTableHeadInster(StringBuilder builder, ExcelDatas exceldatas, string cellgs, string useStr, bool inDB, Action<string> show)
        {
            HashSet<string> tablenames = new HashSet<string>();
            foreach (var datas in exceldatas.Datas)
            {
                //if (datas.Value.Rows.Count > 0)
                {
                    string fileName = datas.Key;
                    string sheetName = datas.Value.SheetName;
                    if (tablenames.Add(sheetName))
                    {
                        show("创建数据表：" + sheetName);
                        List<ExcelData.ExcelDataRow> rows = datas.Value.Rows.Values.ToList();
                        int rowCount = rows.Count;
                        if (rowCount < 1)
                        {
                            show("数据表：" + sheetName + ", 没有数据");
                            continue;
                        }
                        builder.AppendLine("drop table if exists " + sheetName + ";").AppendLine("CREATE TABLE " + sheetName + "(").AppendLine(rows[0].ToMysqlString(cellgs)).AppendLine(");");
                    }
                }
            }
            return true;
        }

        public bool CreateTableRowInster(StringBuilder builder, ExcelDatas exceldatas, string cellgs, string useStr, bool inDB, Action<string> show)
        {
            HashSet<string> tablenames = new HashSet<string>();
            foreach (var itemkey in exceldatas.SheetName_And_FileName.Keys)
            {
                var datas = exceldatas.Datas[itemkey];
                if (datas.Rows.Count() < 1)
                {
                    continue;
                }
                StringBuilder sqlfileBuilder = new StringBuilder();

                string sheetName = datas.SheetName;
                string fileName = itemkey;
                if (!tablenames.Add(sheetName))
                {
                    continue;
                }
                StringBuilder sqlBuilder = new StringBuilder();
                List<ExcelData.ExcelDataRow.ExcelDataCell> cellNames = new List<ExcelData.ExcelDataRow.ExcelDataCell>();

                var rows = datas.Rows.Values.ToArray();
                {
                    var cells = rows[0].Cells;
                    sqlBuilder.Append("INSERT " + sheetName + " (");
                    bool isdouhao = false;

                    int k = 0;
                    foreach (var item in cells)
                    {

                        if (item.Value.Cells.Equals("ALL") || cellgs.Equals("ALL") || item.Value.Cells.Equals(cellgs))
                        {
                            cellNames.Add(item.Value);
                            if (isdouhao) { sqlBuilder.Append(", "); }
                            sqlBuilder.Append("`" + item.Value.CellName + "`");
                            isdouhao = true;
                        }

                        if (k == cells.Count - 1)
                        {
                            sqlBuilder.AppendLine(")");
                            sqlBuilder.AppendLine(" VALUES ");
                        }

                        k++;
                    }
                }

                for (int i = 0; i < rows.Length; i++)
                {
                    sqlBuilder.Append("(");
                    var cells = rows[i].Cells;
                    bool isdouhao = false;
                    for (int k = 0; k < cellNames.Count; k++)
                    {
                        ExcelData.ExcelDataRow.ExcelDataCell cellK = cellNames[k];
                        ExcelData.ExcelDataRow.ExcelDataCell cell = null;
                        foreach (var item in cells)
                        {
                            if (item.Value.CellName.Equals(cellK.CellName))
                            {
                                cell = item.Value;
                                break;
                            }
                        }
                        if (cell == null)
                        {
                            cell = cellK;
                        }
                        if (isdouhao) { sqlBuilder.Append(","); }

                        string stringValueOf = cell.CellValue;

                        if (cell.CellValueType.ToUpper().Equals("STRING"))
                        {
                            sqlBuilder.Append("'" + stringValueOf + "'");
                        }
                        else
                        {
                            sqlBuilder.Append(stringValueOf);
                        }
                        isdouhao = true;
                    }

                    if ((i > 0 && i % 1000 == 0))
                    {
                        sqlBuilder.AppendLine(");");

                        sqlfileBuilder.Append(sqlBuilder.ToString());
                        sqlBuilder = new StringBuilder();
                        cellNames = new List<ExcelData.ExcelDataRow.ExcelDataCell>();
                        {
                            sqlBuilder.Append("INSERT " + sheetName + " (");
                            isdouhao = false;

                            int k = 0;

                            foreach (var item in cells)
                            {

                                if (item.Value.Cells.Equals("ALL") || cellgs.Equals("ALL") || item.Value.Cells.Equals(cellgs))
                                {
                                    cellNames.Add(item.Value);
                                    if (isdouhao) { sqlBuilder.Append(", "); }
                                    sqlBuilder.Append("`" + item.Value.CellName + "`");
                                    isdouhao = true;
                                }

                                if (k == cells.Count - 1)
                                {
                                    sqlBuilder.AppendLine(")");
                                    sqlBuilder.AppendLine(" VALUES ");
                                }

                                k++;
                            }
                        }
                    }
                    else if (i == rows.Length - 1)
                    {
                        sqlBuilder.AppendLine(");");
                        sqlfileBuilder.Append(sqlBuilder.ToString());
                    }
                    else
                    {
                        sqlBuilder.AppendLine("),");
                    }
                }
                builder.Append(sqlfileBuilder.ToString());
                //CreateClassFile(fileName + "_Data", sqlfileBuilder.ToString());
            }
            return true;
        }
    }
}
